package com.hualude.roadsign.repository;

import com.hualude.roadsign.dto.HighLeverDto;
import com.hualude.roadsign.dto.HighLeverFaultDto;
import com.hualude.roadsign.dto.DTHighLeverFaultDto;
import com.hualude.roadsign.dto.DTRequestDto;
import org.hibernate.query.NativeQuery;
import org.hibernate.transform.Transformers;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.util.List;

/**
 * Created by xu_jiaHui on 2018/3/24.
 */
public class HighLeverRepositoryImpl {
    @PersistenceContext
    private EntityManager em;

    public List<HighLeverDto> getHighLever4DT(DTRequestDto dtRequestDto,
                                              String actorIds,
                                              Integer orgId,
                                              Boolean audit,
                                              String startRoad,
                                              String endRoad,
                                              String roadName,
                                              Integer alarmBinding) throws Exception {
        String sql = "select m.*, ur.UserName as highLeverCreatorName, " +
                "og1.OrgName as highLeverOrgName, og2.OrgName as highLeverMainOrgName, a.deviceState " +
                "from (tb_highLever m, tb_user ur, tb_org og1, tb_org og2) " +
                "LEFT JOIN tb_alarm a on (m.AlarmDeviceId= a.DeviceId) " +
                "where ur.UserId = m.highLeverCreatorId " +
                "and og1.OrgId = m.highLeverOrgId and og2.OrgId = m.highLeverMainOrgId and " +
                "m.highLeverRoadName like '%" + roadName + "%' and m.highLeverStartRoad like '%" + startRoad + "%' " +
                "and m.highLeverEndRoad like '%" + endRoad + "%' and m.ProjectNumber like '%" + dtRequestDto.getSearch() + "%' ";
        if (audit)
            sql += "and highLeverIsComplete = 0 ";
        if (alarmBinding != null) {
            if (alarmBinding == 1)
                sql += " and AlarmDeviceId is not NULL ";
            else if (alarmBinding == 2)
                sql += " and AlarmDeviceId is NULL ";
        }

        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += "and m.highLeverMainOrgId=" + orgId + " ";
        else
            sql += "and m.highLeverOrgId = " + orgId + " ";
//        if (alarmType.contains("2"))
//            sql += " and m.AlarmDeviceId is NULL ";
//        else if(alarmType.contains("1"))
//            sql += " and m.AlarmDeviceId is not NULL ";
//        else
        sql += "";
        if (startRoad != "" || endRoad != "") {
            sql += "union select m.*, ur.UserName as highLeverCreatorName, " +
                    "og1.OrgName as highLeverOrgName, og2.OrgName as highLeverMainOrgName, a.deviceState " +
                    "from (tb_highLever m, tb_user ur, tb_org og1, tb_org og2) " +
                    "LEFT JOIN tb_alarm a on (m.AlarmDeviceId= a.DeviceId) " +
                    "where ur.UserId = m.highLeverCreatorId " +
                    "and og1.OrgId = m.highLeverOrgId and og2.OrgId = m.highLeverMainOrgId and " +
                    "m.highLeverRoadName like '%" + roadName + "%' and m.highLeverStartRoad like '%" + endRoad + "%' " +
                    "and m.highLeverEndRoad like '%" + startRoad + "%' and m.ProjectNumber like '%" + dtRequestDto.getSearch() + "%' ";
            if (audit)
                sql += "and highLeverIsComplete = 0 ";

            if (alarmBinding != null) {
                if (alarmBinding == 1)
                    sql += " and AlarmDeviceId is not NULL ";
                else if (alarmBinding == 2)
                    sql += " and AlarmDeviceId is NULL ";
            }
            if (actorIds.indexOf('1') >= 0)
                sql += "";
            else if (actorIds.indexOf('7') >= 0)
                sql += "and m.highLeverMainOrgId=" + orgId + " ";
            else
                sql += "and m.highLeverOrgId = " + orgId + " ";
//            if (alarmType.contains("2"))
//                sql += " and m.AlarmDeviceId is NULL ";
//            else if(alarmType.contains("1"))
//                sql += " and m.AlarmDeviceId is not NULL ";
//            else
//                sql += "";
        }
        sql += "order by highLeverInstallTime desc";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(HighLeverDto.class));
        return query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
    }

    public Integer getHighLever4DTCount(DTRequestDto dtRequestDto,
                                        String actorIds,
                                        Integer orgId,
                                        Boolean audit,
                                        String startRoad,
                                        String endRoad,
                                        String roadName,
                                        Integer alarmBinding) throws Exception {
        String sql1 = "select count(*) from tb_highLever m " +
                "where m.highLeverRoadName like '%" + roadName + "%' and m.highLeverStartRoad like '%" + startRoad + "%' " +
                "and m.highLeverEndRoad like '%" + endRoad + "%' and m.ProjectNumber like '%" + dtRequestDto.getSearch() + "%' ";
        if (audit)
            sql1 += "and highLeverIsComplete = 0 ";
        if (alarmBinding != null) {
            if (alarmBinding == 1)
                sql1 += " and AlarmDeviceId is not NULL ";
            else if (alarmBinding == 2)
                sql1 += " and AlarmDeviceId is NULL ";
        }
        if (actorIds.indexOf('1') >= 0)
            sql1 += "";
        else if (actorIds.indexOf('7') >= 0)
            sql1 += "and m.highLeverMainOrgId=" + orgId + " ";
        else
            sql1 += "and m.highLeverOrgId = " + orgId + " ";

        Query query1 = em.createNativeQuery(sql1);
        Integer count1 = Integer.parseInt(query1.getSingleResult().toString());
        Integer count2 = 0;
        if (startRoad != "" || endRoad != "") {
            String sql2 = "select count(*) from tb_highLever m " +
                    "where m.highLeverRoadName like '%" + roadName + "%' and m.highLeverStartRoad like '%" + endRoad + "%' " +
                    "and m.highLeverEndRoad like '%" + startRoad + "%' and m.ProjectNumber like '%" + dtRequestDto.getSearch() + "%' ";
            if (audit)
                sql2 += "and highLeverIsComplete = 0 ";
            if (alarmBinding != null) {
                if (alarmBinding == 1)
                    sql2 += " and AlarmDeviceId is not NULL ";
                else if (alarmBinding == 2)
                    sql2 += " and AlarmDeviceId is NULL ";
            }
            if (actorIds.indexOf('1') >= 0)
                sql2 += "";
            else if (actorIds.indexOf('7') >= 0)
                sql2 += "and m.highLeverMainOrgId=" + orgId + " ";
            else
                sql2 += "and m.highLeverOrgId = " + orgId + " ";
            Query query2 = em.createNativeQuery(sql2);
            count2 = Integer.parseInt(query2.getSingleResult().toString());
        }
        return count2 + count1;
    }

    public List<String> getStartRoadByRoadName(String roadName, String actorIds, Integer orgId) throws Exception {
        String sql = "select  HighLeverStartRoad as roadName from tb_highLever where HighLeverRoadName like '%" + roadName + "%' ";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += "and highLeverMainOrgId=" + orgId + " ";
        else
            sql += "and highLeverOrgId = " + orgId + " ";
        sql += "union select HighLeverEndRoad as roadName from tb_highLever where HighLeverRoadName like '%" + roadName + "%' ";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += "and highLeverMainOrgId=" + orgId + " ";
        else
            sql += "and highLeverOrgId = " + orgId + " ";
        Query query = em.createNativeQuery(sql);
        return query.getResultList();
    }

    public List<String> getEndRoadByStartRoad(String city, String highLeverRoadName, String startRoad, String actorIds, Integer orgId) throws Exception {
        String sql = "select HighLeverStartRoad as roadName from tb_highLever where HighLeverRoadName like '%" + highLeverRoadName + "%'" +
                " and  HighLeverEndRoad like '%" + startRoad + "%' and HighLeverCity like '%" + city + "%' ";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += "and highLeverMainOrgId=" + orgId + " ";
        else
            sql += "and highLeverOrgId = " + orgId + " ";
        sql += "union select HighLeverEndRoad as roadName from tb_highLever where HighLeverRoadName like '%" + highLeverRoadName + "%'" +
                " and  HighLeverStartRoad like '%" + startRoad + "%' and HighLeverCity like '%" + city + "%' ";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += "and highLeverMainOrgId=" + orgId + " ";
        else
            sql += "and highLeverOrgId = " + orgId + " ";
        Query query = em.createNativeQuery(sql);
        return query.getResultList();
    }

    public List<HighLeverDto> getHighLever4APP(String actorIds, Integer orgId, Integer mCount, Integer mSize, String city, String startRoad, String endRoad, String roadName, String alarmType) throws Exception {
        String sql = "select m.*, ur.UserName as highLeverCreatorName, " +
                "og1.OrgName as highLeverOrgName, og2.OrgName as highLeverMainOrgName,a.deviceState " +
                "from (tb_highLever m, tb_user ur, tb_org og1, tb_org og2) " +
                "left join tb_alarm a on (m.AlarmDeviceId= a.DeviceId) " +
                "where ur.UserId = m.HighLeverCreatorId " +
                "and og1.OrgId = m.HighLeverOrgId and og2.OrgId = m.HighLeverMainOrgId " +
                "and m.HighLeverStartRoad like '%" + startRoad + "%' and m.HighLeverEndRoad like '%" + endRoad + "%' " +
                "and m.HighLeverRoadName like '%" + roadName + "%' and m.HighLeverCity like '%" + city + "%' ";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += " and m.HighLeverMainOrgId=" + orgId + " ";
        else
            sql += " and m.HighLeverOrgId = " + orgId + " ";
        if (alarmType.contains("0"))
            sql += " and m.AlarmDeviceId is NULL ";
        else if (alarmType.contains("1"))
            sql += " and m.AlarmDeviceId is not NULL ";
        else
            sql += "";
        if (startRoad != "" || endRoad != "") {
            sql += "union select m.*, ur.UserName as highLeverCreatorName," +
                    "og1.OrgName as highLeverOrgName, og2.OrgName as highLeverMainOrgName,a.deviceState " +
                    "from (tb_highLever m, tb_user ur, tb_org og1, tb_org og2) " +
                    "left join tb_alarm a on (m.AlarmDeviceId= a.DeviceId) " +
                    "where ur.UserId = m.HighLeverCreatorId " +
                    "and og1.OrgId = m.HighLeverOrgId and og2.OrgId = m.HighLeverMainOrgId " +
                    "and m.HighLeverStartRoad like '%" + endRoad + "%' and m.HighLeverEndRoad like '%" + startRoad + "%' " +
                    "and m.HighLeverRoadName like '%" + roadName + "%' and m.HighLeverCity like '%" + city + "%' ";
            if (actorIds.indexOf('1') >= 0)
                sql += "";
            else if (actorIds.indexOf('7') >= 0)
                sql += "and m.HighLeverMainOrgId=" + orgId + " ";
            else
                sql += "and m.HighLeverOrgId = " + orgId + " ";
            if (alarmType.contains("0"))
                sql += " and m.AlarmDeviceId is NULL ";
            else if (alarmType.contains("1"))
                sql += " and m.AlarmDeviceId is not NULL ";
            else
                sql += "";
        }
        sql += "order by concat(HighLeverInstallTime, highLeverId) desc";

        //System.out.println(sql);
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(HighLeverDto.class));
        return query.setFirstResult((mCount - 1) * mSize).setMaxResults(mSize).getResultList();
    }

    public List<HighLeverDto> getHighLeverById(Integer highLeverId) throws Exception {
        String sql = "select se.* ,o1.orgName as highLeverOrgName,o2.OrgName as highLeverMainOrgName," +
                " u.userName as highLeverCreatorName from tb_highLever se,tb_org o1,tb_user u,tb_org o2 " +
                "where o1.orgId=se.highLeverOrgId and o2.orgId=se.highLeverMainOrgId and u.UserId=se.highLeverCreatorId" +
                " and se.highLeverId=" + highLeverId + " ";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(HighLeverDto.class));
        return query.getResultList();
    }

    public List<String> getCity4HighLever4App(String actorIds, Integer orgId, Integer mCount, Integer mSize) throws Exception {
        String sql = "select DISTINCT HighLeverCity from tb_highLever ";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += " where HighLeverMainOrgId=" + orgId + " ";
        else
            sql += " where HighLeverOrgId = " + orgId + " ";
        Query query = em.createNativeQuery(sql);
        return query.setFirstResult((mCount - 1) * mSize).setMaxResults(mSize).getResultList();
    }

    public List<String> getRoadName4HighLeverByCity(String actorIds, Integer orgId, String city, Integer mCount, Integer mSize) throws Exception {
        String sql = "select DISTINCT HighLeverRoadName from  tb_highLever where HighLeverCity like '%" + city + "%' ";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += " and HighLeverMainOrgId=" + orgId + " ";
        else
            sql += " and HighLeverOrgId = " + orgId + " ";
        Query query = em.createNativeQuery(sql);
        return query.setFirstResult((mCount - 1) * mSize).setMaxResults(mSize).getResultList();
    }

    public List<String> getEndRoadByStartRoad4App(String city, String highLeverRoadName, String startRoad, String actorIds, Integer orgId, Integer mCount, Integer mSize) {
        String sql = "select HighLeverStartRoad as roadName from tb_highLever where HighLeverRoadName like '%" + highLeverRoadName + "%'" +
                " and  HighLeverEndRoad like '%" + startRoad + "%' and HighLeverCity like '%" + city + "%' ";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += "and highLeverMainOrgId=" + orgId + " ";
        else
            sql += "and highLeverOrgId = " + orgId + " ";
        sql += "union select HighLeverEndRoad as roadName from tb_highLever where HighLeverRoadName like '%" + highLeverRoadName + "%'" +
                " and  HighLeverStartRoad like '%" + startRoad + "%' and HighLeverCity like '%" + city + "%' ";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += "and highLeverMainOrgId=" + orgId + " ";
        else
            sql += "and highLeverOrgId = " + orgId + " ";
        Query query = em.createNativeQuery(sql);
        return query.setFirstResult((mCount - 1) * mSize).setMaxResults(mSize).getResultList();
    }

    public List<String> getStartRoadByRoadName4App(String city, String roadName, String actorIds, Integer orgId, Integer mCount, Integer mSize) throws Exception {
        String sql = "select  HighLeverStartRoad as roadName from tb_highLever where HighLeverRoadName like '%" + roadName + "%' " +
                "and HighLeverCity like '%" + city + "%' ";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += "and highLeverMainOrgId=" + orgId + " ";
        else
            sql += "and highLeverOrgId = " + orgId + " ";
        sql += "union select HighLeverEndRoad as roadName from tb_highLever where HighLeverRoadName like '%" + roadName + "%' " +
                "and HighLeverCity like '%" + city + "%' ";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += "and highLeverMainOrgId=" + orgId + " ";
        else
            sql += "and highLeverOrgId = " + orgId + " ";
        Query query = em.createNativeQuery(sql);
        return query.setFirstResult((mCount - 1) * mSize).setMaxResults(mSize).getResultList();
    }

    public List<HighLeverDto> getHighLever4Map4App(Integer orgId,
                                                   String actorIds,
                                                   double neLongitude,
                                                   double neLatitude,
                                                   double swLongitude,
                                                   double swLatitude) throws Exception {
        String sql = "select * from tb_highLever ";
        sql += "where highLeverIsComplete = 1 " +
                "and highLeverLongitude < :neLongitude and highLeverLongitude > :swLongitude " +
                "and highLeverLatitude < :neLatitude and highLeverLatitude > :swLatitude ";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += "and highLeverMainOrgId=" + orgId + " ";
        else
            sql += "and highLeverOrgId = " + orgId + " ";
        Query query = em.createNativeQuery(sql);
        query.setParameter("neLongitude", String.valueOf(neLongitude));
        query.setParameter("swLongitude", String.valueOf(swLongitude));
        query.setParameter("neLatitude", String.valueOf(neLatitude));
        query.setParameter("swLatitude", String.valueOf(swLatitude));
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(HighLeverDto.class));
        return query.getResultList();
    }

    public List<HighLeverFaultDto> getHighLeverCoordinate4Map(Integer orgId, Integer actorId) throws Exception {
        String sql = "SELECT c.HighLeverId,c.HighLeverLongitude,c.HighLeverLatitude,c.HighLeverPhoto\n" +
                "FROM tb_highLever c WHERE HighLeverIsComplete=1 ";
        if (actorId != 1) {
            sql += " and  c.HighLeverOrgId=" + orgId + " ";
        }
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(HighLeverFaultDto.class));
        return query.getResultList();
    }

    public Integer getHighLeverFaultReportCount4DT(String search, Integer actorId, Integer orgId, String highLeverRoadName, String highLeverStartRoad, String highLeverEndRoad) throws Exception {
        String sql = "select count(*) " +
                "FROM ((tb_highLever c JOIN tb_org o ON o.OrgId=c.HighLeverOrgId)\n" +
                "JOIN tb_user u ON u.UserId=c.HighLeverCreatorId)\n" +
                "WHERE c.HighLeverRoadName LIKE '%" + highLeverRoadName + "%' AND c.HighLeverStartRoad LIKE '%" + highLeverStartRoad + "%' AND c.HighLeverEndRoad LIKE '%" + highLeverEndRoad + "%'";
        if (actorId != 1) {
            sql += " and c.HighLeverOrgId=" + orgId + "";
        }
        Query query = em.createNativeQuery(sql);
        return Integer.parseInt(query.getSingleResult().toString());
    }

    public List<DTHighLeverFaultDto> getHighLeverFaultReport4DT(DTRequestDto dtRequestDto, Integer actorId, Integer orgId, String highLeverRoadName, String highLeverStartRoad, String highLeverEndRoad) throws Exception {
        String sql = "SELECT c.HighLeverId,c.HighLeverPhoto,c.HighLeverProvince,c.HighLeverCity," +
                "c.HighLeverArea,c.HighLeverRegionId,o.OrgName AS highLeverOrgName,u.UserName AS highLeverCreatorName," +
                "c.HighLeverInstallTime\n" +
                "FROM ((tb_highLever c JOIN tb_org o ON o.OrgId=c.HighLeverOrgId)\n" +
                "JOIN tb_user u ON u.UserId=c.HighLeverCreatorId)\n" +
                "WHERE c.HighLeverRoadName LIKE '%" + highLeverRoadName + "%' AND c.HighLeverStartRoad LIKE '%" + highLeverStartRoad + "%' AND c.HighLeverEndRoad LIKE '%" + highLeverEndRoad + "%'";
        if (actorId != 1) {
            sql += " and c.HighLeverOrgId=" + orgId + "";
        }
        if (highLeverStartRoad != "" || highLeverEndRoad != "") {
            sql += " UNION SELECT c.HighLeverId,c.HighLeverPhoto,c.HighLeverProvince,c.HighLeverCity," +
                    "c.HighLeverArea,c.HighLeverRegionId,o.OrgName AS highLeverOrgName,u.UserName AS highLeverCreatorName," +
                    "c.HighLeverInstallTime\n" +
                    "FROM ((tb_highLever c JOIN tb_org o ON o.OrgId=c.HighLeverOrgId)\n" +
                    "JOIN tb_user u ON u.UserId=c.HighLeverCreatorId)\n" +
                    "WHERE c.HighLeverRoadName LIKE '%" + highLeverRoadName + "%' AND c.HighLeverStartRoad LIKE '%" + highLeverEndRoad + "%' AND c.HighLeverEndRoad LIKE '%" + highLeverStartRoad + "%'";
        }
        if (actorId != 1) {
            sql += " and c.HighLeverOrgId=" + orgId + "";
        }
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(DTHighLeverFaultDto.class));
        List<DTHighLeverFaultDto> highLeverFaultDtos = query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
        return highLeverFaultDtos;
    }

    public List<String> getStartRoad(String roadName, Integer actorId, Integer orgId) throws Exception {
        String sql = "SELECT c.HighLeverStartRoad AS highLeverStartRoad\n" +
                "FROM tb_highLever c " +
                "WHERE c.HighLeverRoadName LIKE '%" + roadName + "%'";
        if (actorId != 1) {
            if (actorId == 7)
                sql += " and c.HighLeverMainOrgId=" + orgId + " ";
            else
                sql += " and c.HighLeverOrgId=" + orgId + " ";
        }
        sql += " UNION SELECT c.HighLeverEndRoad AS highLeverStartRoad\n" +
                "FROM tb_highLever c " +
                "WHERE c.HighLeverRoadName LIKE '%" + roadName + "%'";
        if (actorId != 1) {
            if (actorId == 7)
                sql += " and c.HighLeverMainOrgId=" + orgId + " ";
            else
                sql += " and c.HighLeverOrgId=" + orgId + " ";
        }
        Query query = em.createNativeQuery(sql);
        return query.getResultList();
    }

    public List<String> getSectionByStartRoad(String roadName, Integer actorId, Integer orgId) throws Exception {
        String sql = "SELECT c.HighLeverStartRoad AS highLeverStartRoad\n" +
                "FROM tb_highLever c\n" +
                "WHERE c.HighLeverEndRoad LIKE '%" + roadName + "%'";
        if (actorId != 1) {
            if (actorId == 7)
                sql += " and c.HighLeverMainOrgId=" + orgId + " ";
            else
                sql += " and c.HighLeverOrgId=" + orgId + " ";
        }
        sql += " UNION SELECT c.HighLeverEndRoad AS highLeverStartRoad\n" +
                "FROM tb_highLever c\n" +
                "WHERE c.HighLeverStartRoad LIKE '%" + roadName + "%'";
        if (actorId != 1) {
            if (actorId == 7)
                sql += " and c.HighLeverMainOrgId=" + orgId + " ";
            else
                sql += " and c.HighLeverOrgId=" + orgId + " ";
        }
        Query query = em.createNativeQuery(sql);
        return query.getResultList();
    }

    public List<HighLeverDto> getHighLeverCoordinates4Map(Integer orgId, Integer orgType, Integer actorId) throws Exception {
        String sql = "select HighLeverId, HighLeverLongitude as longitude , HighLeverLatitude as latitude from tb_highLever ";
        if (actorId != 1) {
            if (orgType == 0)
                sql += " where highLeverOrgId=" + orgId + " ";
            else if (orgType == 1)
                sql += " where highLeverMainOrgId=" + orgId + " ";
        }
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(HighLeverDto.class));
        return query.getResultList();
    }

    public Integer getHighLeverCount(String actorIds, Integer orgId) throws Exception {
        String sql = "select count(*) from tb_highLever ";
        if (!actorIds.contains("1")) {
            sql += " where HighLeverOrgId=" + orgId + "";
        }
        Query query = em.createNativeQuery(sql);
        return (Integer) query.getSingleResult();
    }


    public List<HighLeverDto> getDeviceInfo(Integer deviceId) throws Exception {
        String sql = "select th.HighLeverId,th.ProjectNumber,th.HighLeverRegionId,th.HighLeverLongitude,th.HighLeverLatitude,th.HighLeverProvince,th.HighLeverCity," +
                "th.HighLeverArea,th.HighLeverRoadName,th.HighLeverStartRoad,th.HighLeverEndRoad,th.HighLeverSize,th.HighLeverPhoto,th.HighLeverInstallTime," +
                "th.HighLeverInstallOrg,th.HighLeverPeriod,th.HighLeverServiceLife,th.HighLeverRemark,th.HighLeverCreatorId,th.HighLeverModifierId,th.HighLeverOrgId," +
                "th.HighLeverMainOrgId,th.HighLeverIsComplete,th.HighLeverPatrolTime,th.HighLeverPatrolContent,th.AlarmCount,th.AlarmDeviceId," +
                "o1.OrgName as highLeverOrgName,o2.OrgName as highLeverMainOrgName " +
                "from tb_highLever th,tb_org o1,tb_org o2 where o1.OrgId = th.HighLeverOrgId and o2.OrgId = th.HighLeverMainOrgId and th.HighLeverId = " + deviceId + "";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(HighLeverDto.class));
        return query.getResultList();
    }


}

